package DaosImpl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

import Daos.RoomDao;
import Beans.DataBase;
import Beans.QueryRoomInfo;
import Beans.Room;
import Beans.RoomType;

public class RoomDaoImpl implements RoomDao{

	@Override
	public ArrayList queryAllRooms() throws Exception {
		Connection conn = null;
        ArrayList roomList = new ArrayList();
        ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			String sql = "select * from Room";
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
            while(rs.next()){
            	Room room = new Room();
            	room.setRoomID(Integer.parseInt(rs.getString("r_id")));
            	room.setRoomTypeID(Integer.parseInt(rs.getString("rt_id")));
            	room.setRoomStatus(rs.getString("r_status"));
            	roomList.add(room);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
            }catch (Exception ex){
            	
            }
        }
        return roomList;
	}

	@Override
	public ArrayList queryAllRoomTypes() throws Exception {
		Connection conn = null;
        ArrayList roomTypeList = new ArrayList();
        ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			String sql = "select * from Room_Type";
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
            while(rs.next()){
            	RoomType roomType = new RoomType();
            	roomType.setRoomTypeID(Integer.parseInt(rs.getString("rt_id")));
            	roomType.setRoomTypeName(rs.getString("rt_name"));
            	roomType.setRoomTypeSize(Float.parseFloat(rs.getString("rt_size")));
            	roomType.setRoomRent(Float.parseFloat(rs.getString("rt_rent_money")));
            	roomType.setRoomEarnest(Float.parseFloat(rs.getString("rt_earnest")));
            	roomTypeList.add(roomType);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
            }catch (Exception ex){
            	
            }
        }
        return roomTypeList;
	}

	@Override
	public float getRoomOneRent(int roomId) throws Exception {
		Connection conn = null;
		float money = 0;
		ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			String sql = "select rt_rent_money from Room,Room_type where room.rt_id=Room_Type.rt_id and Room.r_id='"+roomId+"';";
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
            while(rs.next()){
            	money = rs.getFloat(1);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
            }catch (Exception ex){
            	
            }
        }
        return money;
	}

	@Override
	public ArrayList<QueryRoomInfo> getRoomInfo(int roomId) throws Exception {
	 	Connection conn = null;
        ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        QueryRoomInfo room = null;
        ArrayList<QueryRoomInfo> roomList = new ArrayList<QueryRoomInfo>();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			String sql = "SELECT r_id,rt_name,rt_rent_money,rt_earnest,r_status FROM Room,Room_Type WHERE r_id LIKE '%"+roomId+"%' and Room.rt_id=Room_Type.rt_id;";
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
            while(rs.next()){
            	room = new QueryRoomInfo();
            	room.setRoomid(Integer.parseInt(rs.getString("r_id")));
            	room.setStatus(rs.getString("r_status"));
            	room.setRoomname(rs.getString("rt_name"));
            	room.setRent(Float.parseFloat(rs.getString("rt_rent_money")));
            	room.setEarnest(Float.parseFloat(rs.getString("rt_earnest")));
            	roomList.add(room);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
            }catch (Exception ex){
            	ex.printStackTrace();
            }
        }
        return roomList;
	}
	
}
